﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace Soul.Excel
{
    public class ExcelDocument
    {
        public List<ExcelDataTable> Tables { get; } = new List<ExcelDataTable>();

        #region Write
        private IWorkbook OpenWorkbook(bool isXlsx, Stream stream)
        {
            if (isXlsx)
            {
                return new XSSFWorkbook(stream);
            }
            return new HSSFWorkbook(stream);
        }

        public void Write(string file, bool isXlsx = false)
        {
            using (var fs = new FileStream(file, FileMode.Create))
            {
                Write(fs, isXlsx);
            }
        }

        public void Write(Stream stream, bool isXlsx = false)
        {
            IWorkbook document;
            if (isXlsx)
            {
                document = new XSSFWorkbook();
            }
            else
            {
                document = new HSSFWorkbook();
            }
            foreach (var table in Tables)
            {
                document.AddSheet(table);
               
            }
            document.Write(stream);
        }

        #endregion

        #region Read
        public ExcelDataTable Read(string file, Action<ExcelReaderOptions> configure)
        {
            using (var fs = new FileStream(file, FileMode.Open))
            {
                return Read(fs, configure);
            }
        }

        public ExcelDataTable Read(Stream stream, Action<ExcelReaderOptions> configure)
        {
            var options = new ExcelReaderOptions();
            configure(options);
            var document = OpenWorkbook(options.IsXlsx, stream);
            var sheet = document.GetSheetAt(options.SheetIndex);
            return ReadSheet(sheet, options);
        }

        public ExcelDocument ReadAll(string file, Action<ExcelReaderOptions> configure)
        {
            using (var fs = new FileStream(file, FileMode.Open))
            {
                return ReadAll(fs, configure);
            }
        }

        public ExcelDocument ReadAll(Stream stream, Action<ExcelReaderOptions> configure)
        {
            var document = new ExcelDocument();
            var options = new ExcelReaderOptions();
            configure(options);
            var book = OpenWorkbook(options.IsXlsx, stream);
            for (int i = options.SheetIndex; i < book.NumberOfSheets; i++)
            {
                var sheet = book.GetSheetAt(i);
                var table = ReadSheet(sheet, options);
                document.Tables.Add(table);
            }
            return document;
        }

        public ExcelDataTable ReadSheet(ISheet sheet, ExcelReaderOptions options)
        {
            var table = new ExcelDataTable();
            if (options.HasColumn)
            {
                var columnRow = sheet.GetRow(options.RowIndex);
                for (int i = 0; i < columnRow.Cells.Count; i++)
                {
                    var name = columnRow.GetCell(i);
                    table.Columns.Add(name.StringCellValue);
                }
            }
            else
            {
                foreach (var item in options.Columns)
                {
                    table.Columns.Add(item);
                }
            }
            var startRow = options.HasColumn ? options.RowIndex + 1 : options.RowIndex;
            for (int i = startRow; i <= sheet.PhysicalNumberOfRows; i++)
            {
                var row = sheet.GetRow(i);
                if (row == null || row.Cells == null || row.Cells.All(a => string.IsNullOrEmpty(a.ToString())))
                {
                    continue;
                }
                var columnIndex = 0;
                var dataRow = table.NewRow();
                foreach (var item in table.Columns)
                {
                    var cell = row.Cells.Where(c => c.ColumnIndex == columnIndex).FirstOrDefault();
                    if (cell == null)
                    {
                        dataRow[item.Name] = null;
                    }
                    else
                    {
                        var value = GetCellValue(cell);
                        dataRow[item.Name] = value;
                    }
                    columnIndex++;
                }
                table.Rows.Add(dataRow);
            }
            return table;
        }

        private object GetCellValue(ICell cell)
        {
            if (cell.CellType == CellType.Numeric)
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return cell.DateCellValue;
                }
                return cell.NumericCellValue;
            }
            else if (cell.CellType == CellType.Formula)
            {
                if (cell.CachedFormulaResultType == CellType.String)
                {
                    return cell.StringCellValue;
                }
                return cell.NumericCellValue;
            }
            else if (cell.CellType == CellType.Boolean)
            {
                return cell.BooleanCellValue;
            }
            else if (cell.CellType == CellType.Blank)
            {
                return null;
            }
            else if (cell.CellType == CellType.String)
            {
                return cell.StringCellValue; ;
            }
            else
            {
                return cell.ToString();
            }

        }
        #endregion
    }
}
